
package com.hex.ds.em.hcdp.pack.service.impl;

import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.io.resource.Resource;
import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.StrUtil;
import com.hex.ds.em.hcdp.common.entity.ScriptPackScanResultEntity;
import com.hex.ds.em.hcdp.common.utils.FileUtil;
import com.hex.ds.em.hcdp.common.utils.PoiUtil;
import com.hex.ds.em.hcdp.impala.service.ImpalaBaseSqlVisitor;
import com.hex.ds.em.hcdp.pack.dao.IScanFileStatusRepository;
import com.hex.ds.em.hcdp.pack.dao.IScanItemRepository;
import com.hex.ds.em.hcdp.pack.entity.ScriptFileStatusEntity;
import com.hex.ds.em.hcdp.pack.entity.ScriptItemEntity;
import com.hex.ds.em.hcdp.pack.entity.ScriptPackScanListEntity;
import com.hex.ds.em.hcdp.pack.service.ISQLExtractor;
import com.hex.ds.em.hcdp.pack.service.IScriptPackTaskService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 扫描业务代码
 *
 * @author ky.zhang
 */
@Slf4j
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class ScriptPackTaskServiceImpl implements IScriptPackTaskService {

    private final ISQLExtractor isqlExtractor;

    private final IScanFileStatusRepository scanFileStatusRepository;

    private final IScanItemRepository scanItemRepository;


    @Value("${script.pack.scriptPath}")
    private String scriptFilePath;

    @Value("${script.pack.impalaPath}")
    private String impalaFilePaths;

    @Value("${script.pack.scanDispositionPath}")
    private String scanExcelPath;

    @Value("${script.pack.reportPath}")
    private String reportPath;

    @Value("${script.pack.sqlScanResultPath}")
    private String sqlScanResultPath;

    //impala脚本数量
    private static Integer impalaNum;

    //失败脚本数量
    private static Integer impalaFailNum;

    //失败最多次的脚本
    private final Map<String,Integer> impalaFileFailNumMaxMap = new HashMap<>();

    //失败次数最多的脚本名
    private static String failMaxFileName;

    private final Map<String,Map<String,Integer>> failSqlMap = new HashMap<>();

    private Map<String,String> SqlMap = new HashMap<>();

    //性能类隐患类失败次数
    private final Map<String,Integer> performanceFailMap = new HashMap<>();

    //失败描述
    private List<String> performanceFailList;

    // 指定时区为上海
    ZoneId zoneId = ZoneId.of("Asia/Shanghai");

    /**
     * 扫描业务代码
     */
    @Override
    public void scanFile() throws IOException, InvalidFormatException {
        //执行前先清空扫描结果目录
        clearDirectory ();
        //读取扫描项配置并存入数据库
        List<ScriptItemEntity> scriptItemEntityList = readScanExcel(scanExcelPath);
        //校验目录是否符合格式
        checkDirectory(scriptFilePath);
        //获取impala脚本数量
        impalaNum = countDirectory(impalaFilePaths);
        //读取 impala 目录脚本
        Map<String, String> filePathMap = FileUtil.getFilePaths(impalaFilePaths);
        //创建文件对象
        Map<String, File> fileObjectMap = createFileObjectMap(filePathMap);
        //读取文件信息并存入数据库
        readFileName(fileObjectMap);
        //读取文件内的sql并扫描
        Map<String, List<ScriptPackScanResultEntity>> scanResultMap = readFileMapContent(fileObjectMap, scriptItemEntityList);
        //脚本扫描情况统计
        Map<String,List<String>> fileScanResultMap = getFileScanResult(scanResultMap);
        //分类问题统计
        Map<String, List<String>> totalMap = getScanResult(scanResultMap);
        //失败描述top10
        getFailList();
        //错误次数最多脚本Map
        getFailMaxMap();
        //问题较大脚本sql清单
        getFailSqlList();
        //结果导出到word
        exportWord(totalMap,fileScanResultMap);
    }

    private void clearDirectory() {
        // 创建File对象
        File directory = new File(sqlScanResultPath);

        // 检查目录是否存在以及是否为目录
        if (directory.exists() && directory.isDirectory()) {
            // 获取目录中的所有文件
            File[] files = directory.listFiles();

            if (files != null) {
                // 遍历文件并删除它们
                for (File file : files) {
                    if (file.isFile()) {
                        boolean deleted = file.delete();
                        if (deleted) {
                            log.info("Deleted file: " + file.getName());
                        } else {
                            log.error("Failed to delete file: " + file.getName());
                        }
                    }
                }
            } else {
                log.info("结果目录已为空");
            }
        } else {
            log.info("结果目录不存在");
        }
    }


    private void getFailSqlList() {
        if (failSqlMap.containsKey(failMaxFileName)) {
            Map<String, Integer> sqlFailCountMap = failSqlMap.get(failMaxFileName);

            // 使用优先队列（最小堆）来存储出错次数最多的10个SQL语句
            PriorityQueue<Map.Entry<String, Integer>> minHeap = new PriorityQueue<>(
                    Map.Entry.comparingByValue());

            // 遍历sqlFailCountMap，将出错次数和SQL语句添加到最小堆中
            for (Map.Entry<String, Integer> entry : sqlFailCountMap.entrySet()) {
                minHeap.offer(entry);
                // 如果堆的大小超过10，就移除堆顶元素（出错次数最少的元素）
                if (minHeap.size() > 10) {
                    minHeap.poll();
                }
            }

            // 创建一个LinkedHashMap来保持插入顺序
            LinkedHashMap<String, String> sqlMap = new LinkedHashMap<>();

            // 从最小堆中取出元素，此时堆中存储的是出错次数最多的10个SQL语句
            while (!minHeap.isEmpty()) {
                Map.Entry<String, Integer> entry = minHeap.poll();
                String errorType = SqlMap.get(entry.getKey());
                sqlMap.put(entry.getKey(), errorType);
            }
            SqlMap.clear();

            // 现在sqlMap中存储了出错次数最多的10个SQL语句及其错误类型
            this.SqlMap = sqlMap;
        }
    }

    private void getFailList() {
        performanceFailList = performanceFailMap.entrySet()
                .stream()
                .sorted(Map.Entry.<String, Integer>comparingByValue().reversed())
                .map(Map.Entry::getKey)
                .limit(10)
                .collect(Collectors.toList());
    }

    private void getFailMaxMap() {
        int max=0;
        String fileName = "";
        for (Map.Entry<String,Integer> map : impalaFileFailNumMaxMap.entrySet()){
            Integer value = map.getValue();
            if (value>=max){
                max = value;
            }
        }
        for (Map.Entry<String,Integer> map : impalaFileFailNumMaxMap.entrySet()){
            Integer value = map.getValue();
            String key = map.getKey();
            if (value==max){
                fileName = key;
            }
        }
        impalaFileFailNumMaxMap.clear();
        failMaxFileName = fileName;
        impalaFileFailNumMaxMap.put(fileName, max);
    }

    private void getFailFileNum(){
        int failFileNum = 0;
        for (Map.Entry<String,Integer> map : impalaFileFailNumMaxMap.entrySet()){
            failFileNum++;
        }
        impalaFailNum = failFileNum;
    }

    /**
     *获取扫描结果
     * @return fileScanResult Map<文件名, Map<扫描项类型,是否成功>>
     */
    private Map<String, List<String>> getFileScanResult(Map<String, List<ScriptPackScanResultEntity>> scanResultMap) {
        Map<String,List<String>> fileScanResult = new HashMap<>();
        List<String> scanFailTypeList = new ArrayList<>();
        String fileName="";
        for (Map.Entry<String,List<ScriptPackScanResultEntity>> entry : scanResultMap.entrySet()){
            List<ScriptPackScanResultEntity> value = entry.getValue();
            for (ScriptPackScanResultEntity scriptPackScanResultEntity : value) {
                fileName = scriptPackScanResultEntity.getFileName();
                String scanState = scriptPackScanResultEntity.getScanState();
                String scanType = scriptPackScanResultEntity.getScanType();
                String scanItemDescribe = scriptPackScanResultEntity.getScanItemDescribe();
                String scanDescInfo = scriptPackScanResultEntity.getScanDescInfo();
                if (scanState.equals("fail")){
                    ScriptItemEntity scriptItem = new ScriptItemEntity();
                    Optional<ScriptItemEntity> byScanDescAndDeletedFalse = scanItemRepository.findByScanDescAndDeletedFalse(scanItemDescribe);
                    if (byScanDescAndDeletedFalse.isPresent()) {
                        scriptItem = byScanDescAndDeletedFalse.get();
                    }
                    UUID uuid = UUID.fastUUID();
                    scanFileStatusRepository.save(new ScriptFileStatusEntity().setId(uuid.toString()).setFileName(fileName).setStatus("扫描失败").setHandComment(scriptItem.getHandComment()).setScanDescInfo(scanDescInfo).setScanType(scanType));
                    if (scanType.equals("性能类")||scanType.equals("隐患类")){
                        boolean mark = performanceFailMap.containsKey(scanDescInfo);
                        if (!mark){
                            performanceFailMap.put(scanDescInfo,1);
                        } else {
                            Integer i = performanceFailMap.get(scanDescInfo);
                            int sum = i+1;
                            performanceFailMap.put(scanDescInfo,sum);
                        }
                    }
                    if (!fileName.isEmpty()) fileScanResult.put(fileName, scanFailTypeList);
                }
            }
        }
        return fileScanResult;
    }

    private Map<String, List<String>> getScanResult(Map<String, List<ScriptPackScanResultEntity>> scanResultMap) {
        Map<String, List<String>> totalMap = new HashMap<>();
        for(Map.Entry<String, List<ScriptPackScanResultEntity>> entry : scanResultMap.entrySet()){
            String sql = entry.getKey();
            List<String> typeList = new ArrayList<>();
            List<ScriptPackScanResultEntity> scanResultEntities = entry.getValue();
            for (ScriptPackScanResultEntity scanResultEntity : scanResultEntities) {
                String scanType = scanResultEntity.getScanType();
                String scanState = scanResultEntity.getScanState();
                if (scanState.equals("fail")){
                   typeList.add(scanType);
                }
            }
            totalMap.put(sql,typeList);
        }
        return totalMap;
    }

    /**
     *数据导出到word
     */
    private void exportWord( Map<String, List<String>> totalMap, Map<String, List<String>> fileScanResultMap) throws IOException, InvalidFormatException {
        //读取模板word
        Resource resource = new ClassPathResource("Template.docx");
        InputStream stream = resource.getStream();
        // 创建一个Word文档
        XWPFDocument document = new XWPFDocument(stream);
        exportChart(document,totalMap);
        // 定义导出文件的路径和名称
        String fileName = "XX客户大数据任务代码扫描巡检报告.docx";
        String reportDir = reportPath;
        reportPath = reportDir + File.separator + fileName;

        // 将文档写入文件
        try (FileOutputStream out = new FileOutputStream(reportPath)) {
            document.write(out);
            System.out.println("文档已成功导出到: " + reportPath);
        } catch (IOException e) {
            throw new RuntimeException("word导出失败", e);
        } finally {
            try {
                document.close();
            } catch (IOException e) {
                log.error("word导出失败");
            }
        }
    }

    /**
     * 具体数据刷新到doc对象
     *
     * @param doc               word文件对象
     */
    private void exportChart(XWPFDocument doc, Map<String, List<String>> totalMap) throws IOException, InvalidFormatException {
        List<XWPFChart> charts = doc.getCharts();// 获取模版中所有的图表
        int functionNum = 0;
        int performanceNum = 0;
        int hiddenNum = 0;
        int specificationNum = 0;
        for (int i = 0; i < charts.size(); i++) {
          if (i==0){
              XSSFWorkbook workbook = charts.get(i).getWorkbook();
              XSSFSheet sheet = workbook.getSheetAt(0);

              //系列信息
              String[] seriesNames = {""};
              //分类信息
              sheet.getRow(1).getCell(0).setCellValue("Impala");
              //设置数值
              sheet.getRow(1).getCell(1).setCellValue(impalaNum);
              //设置标题
              String title  = "脚本扫描总数"+ (impalaNum);
              PoiUtil.wordExportChar(charts.get(i),title,seriesNames,sheet,1,2);
          }else if (i==1){
              XSSFWorkbook workbook = charts.get(i).getWorkbook();
              XSSFSheet sheet = workbook.getSheetAt(0);
              //系列信息
              String[] seriesNames = {"脚本扫描情况"};
              //分类信息
              sheet.getRow(1).getCell(0).setCellValue("功能类");
              sheet.getRow(2).getCell(0).setCellValue("规范类");
              sheet.getRow(3).getCell(0).setCellValue("性能类");
              sheet.getRow(4).getCell(0).setCellValue("隐患类");
              //设置数值
              for (Map.Entry<String,List<String>> entry : totalMap.entrySet()){
                  List<String> values = entry.getValue();
                  for (String value : values) {
                      if (value.equals("功能类")) functionNum++;
                      if (value.equals("规范类")) specificationNum++;
                      if (value.equals("性能类")) performanceNum++;
                      if (value.equals("隐患类")) hiddenNum++;
                  }
              }
              sheet.getRow(1).getCell(1).setCellValue(functionNum);
              sheet.getRow(2).getCell(1).setCellValue(specificationNum);
              sheet.getRow(3).getCell(1).setCellValue(performanceNum);
              sheet.getRow(4).getCell(1).setCellValue(hiddenNum);
              //设置标题
              String title  = "总计"+ impalaFailNum + "个Impala任务脚本存在代码问题";
              PoiUtil.wordExportChar(charts.get(i),title,seriesNames,sheet,1,4);
          }
        }
        //修改word模板内容
        Integer total = functionNum+specificationNum+performanceNum+hiddenNum;
        PoiUtil.replaceTextInDocument(doc,"one",impalaNum.toString());
        PoiUtil.replaceTextInDocument(doc,"two",impalaFailNum.toString());
        PoiUtil.replaceTextInDocument(doc,"three",getNum(impalaFailNum,impalaNum));
        PoiUtil.replaceTextInDocument(doc,"four",Integer.valueOf(specificationNum).toString());
        PoiUtil.replaceTextInDocument(doc, "five", getNum(specificationNum,total));
        PoiUtil.replaceTextInDocument(doc, "six", getResult("规范类"));
        PoiUtil.replaceTextInDocument(doc, "seven", Integer.valueOf(functionNum).toString());
        PoiUtil.replaceTextInDocument(doc, "eight", getNum(functionNum,total));
        PoiUtil.replaceTextInDocument(doc, "nine", getResult("功能类"));
        PoiUtil.replaceTextInDocument(doc, "ten", Integer.valueOf(performanceNum).toString());
        PoiUtil.replaceTextInDocument(doc, "eleven", getNum(performanceNum,total));
        PoiUtil.replaceTextInDocument(doc, "twelve", getResult("性能类"));
        PoiUtil.replaceTextInDocument(doc, "thirteen", Integer.valueOf(hiddenNum).toString());
        PoiUtil.replaceTextInDocument(doc, "hidden", getNum(hiddenNum,total));
        PoiUtil.replaceTextInDocument(doc, "fifteen", getResult("隐患类"));
        PoiUtil.replaceTextInDocument(doc, "question", getNum(impalaFailNum,impalaNum));
        PoiUtil.replaceTextInDocument(doc,"Test1", String.valueOf(LocalDate.now(zoneId).getYear()));
        PoiUtil.replaceTextInDocument(doc,"Test2",String.valueOf(LocalDate.now(zoneId).getMonthValue()));
        PoiUtil.replaceTextInDocument(doc,"Test3",String.valueOf(LocalDate.now(zoneId).getDayOfMonth()));
        //修改word模板中的表格数据
        List<XWPFTable> tables = doc.getTables();
        for (int i = 0; i < tables.size(); i++) {
            if (i == 0) continue;
            if (i == 1){
                XWPFTable xwpfTable = tables.get(i);
                List<XWPFTableRow> rows = xwpfTable.getRows();
                for (int i1 = 0; i1 < rows.size(); i1++) {
                    if (i1==0)continue;
                    XWPFTableRow xwpfTableRow = rows.get(i1);
                    XWPFTableCell scanDescInfoCell = xwpfTableRow.getCell(1);
                    XWPFTableCell scanHandeCommentCell = xwpfTableRow.getCell(2);
                    if (i1<=performanceFailList.size()){
                        String scanDescInfo = performanceFailList.get(i1 - 1);
                        scanDescInfoCell.setText(scanDescInfo);
                        Optional<ScriptItemEntity> byScanDescInfoAndDeletedFalse = scanItemRepository.findByScanDescInfoAndDeletedFalse(scanDescInfo);
                        if (byScanDescInfoAndDeletedFalse.isPresent()){
                            ScriptItemEntity scriptFileStatusEntity = byScanDescInfoAndDeletedFalse.get();
                            String handComment = scriptFileStatusEntity.getHandComment();
                            scanHandeCommentCell.setText(handComment);
                        }
                    }
                }
            }
            if (i==2){
                XWPFTable xwpfTable = tables.get(i);
                if (xwpfTable != null) {
                    List<XWPFTableRow> rows = xwpfTable.getRows();
                    if (rows != null && !rows.isEmpty()) {
                        int rowIndex = 1; // 从第二行开始填充数据，假设第一行是标题行

                        // 检查SqlMap是否为空
                        if (SqlMap != null && !SqlMap.isEmpty()) {
                            for (Map.Entry<String, String> entry : SqlMap.entrySet()) {
                                if (rowIndex < rows.size()) {
                                    XWPFTableRow xwpfTableRow = rows.get(rowIndex);
                                    if (xwpfTableRow != null) {
                                        XWPFTableCell sqlCell = xwpfTableRow.getCell(1);
                                        XWPFTableCell problemCell = xwpfTableRow.getCell(2);

                                        if (sqlCell != null && problemCell != null) {
                                            String sql = entry.getKey();
                                            String scanType = entry.getValue();
                                            sqlCell.setText(sql);
                                            problemCell.setText(scanType);
                                        }
                                    }
                                    rowIndex++;
                                } else {
                                    break;
                                }
                            }
                        } else {
                            log.error("SqlMap为空");
                        }
                    }
                }
            }
        }
    }

    /**
     * 获取脚本失败扫描类别占总脚本数量
     *
     * @param type 扫描类别
     */
    private String getResult(String type) {
        Set<String> failedFileNames = new HashSet<>();
        List<ScriptFileStatusEntity> scanTypeAndDeletedFalse = scanFileStatusRepository.findByScanTypeAndDeletedFalse(type);
        for (ScriptFileStatusEntity scriptFileStatusEntity : scanTypeAndDeletedFalse) {
            String status = scriptFileStatusEntity.getStatus();
            String fileName = scriptFileStatusEntity.getFileName();
            if ("扫描失败".equals(status)) {
                failedFileNames.add(fileName);
            }
        }
        double count = failedFileNames.size();
        return String.format("%.1f", (count / impalaNum) * 100);
    }

    private String getNum(int Num, Integer total) {
        if (Num==0||total.equals(0)){
            return String.valueOf(0.0);
        }
       return String.format("%.2f",(double)Num/total*100);
    }

    private Map<String,List<ScriptPackScanResultEntity>> readFileMapContent(Map<String, File> fileObjectMap, List<ScriptItemEntity> scriptItemEntityList) {
        Map<String,List<ScriptPackScanResultEntity>> scanResultMap= new HashMap<>();
        for (Map.Entry<String, File> entry : fileObjectMap.entrySet()) {
            String fileName = entry.getKey();
            //单个文件失败次数
            int failNum = 0;
            //脚本是否有临时表操作
            boolean temFlag = false;
            //脚本是否修改数据
            boolean updateDataFlag = false;
            //初始化一个StringBuilder用于拼接失败描述
            StringBuilder failDescBuilder = new StringBuilder();
            File file = entry.getValue();
            ScriptPackScanListEntity scriptPackScanListEntity = new ScriptPackScanListEntity();
            String absolutePath = file.getAbsolutePath();
            scriptPackScanListEntity.setScanFilePath(absolutePath);
            List<String> sqlList = getSqlByReadFile(absolutePath,fileName);
            for (String sql : sqlList) {
                boolean endFlag = false;
                //拼接sql扫描结果
                StringBuilder failSqlDescBuilder = new StringBuilder();

                ImpalaBaseSqlVisitor impalaBaseSqlVisitor = new ImpalaBaseSqlVisitor();
                Map<String, ScriptPackScanResultEntity> scriptPackScanResultMap = initScript(scriptItemEntityList, fileName);

                // 判断是否脚本最后一段sql
                if (sqlList.get(sqlList.size() - 1).equals(sql)) {
                    endFlag = true;
                }

                impalaBaseSqlVisitor.visitRoot(sql, scriptPackScanResultMap, fileName, endFlag,temFlag,updateDataFlag);
                //判断脚本是否使用临时表
                if (impalaBaseSqlVisitor.tem){
                    temFlag = true;
                }
                //判断脚本是否修改数据
                if (impalaBaseSqlVisitor.updateData){
                    updateDataFlag = true;
                }
                List<ScriptPackScanResultEntity> scriptPackScanResultList = impalaBaseSqlVisitor.getScriptPackScanResultList();
                scanResultMap.put(sql, scriptPackScanResultList);

                for (ScriptPackScanResultEntity scriptPackScanResultEntity : scriptPackScanResultList) {
                    String scanState = scriptPackScanResultEntity.getScanState();
                    String scanType = scriptPackScanResultEntity.getScanType();
                    if ("fail".equals(scanState)) {
                        failNum++;
                        impalaFileFailNumMaxMap.put(fileName, failNum);

                        // 更新每个SQL的失败次数
                        failSqlMap.computeIfAbsent(fileName, k -> new HashMap<>()).merge(sql, 1, Integer::max);
                        SqlMap.put(sql,scanType);

                        break;
                    }
                }
                for (ScriptPackScanResultEntity scriptPackScanResultEntity : scriptPackScanResultList) {
                    String scanDescInfo = scriptPackScanResultEntity.getScanDescInfo();
                    String scanState = scriptPackScanResultEntity.getScanState();
                    String scanName = scriptPackScanResultEntity.getScanName();
                    if (scanState.equals("fail")){
                        if (!scanName.equals("endStatisticalTableCheck")&&!scanName.equals("endMetadataRefreshCheck")&&!scanName.equals("temDropCheck")){
                            failSqlDescBuilder.append("--").append(scanDescInfo).append(System.lineSeparator());
                        }
                    }
                }
                if (impalaBaseSqlVisitor.updateData){
                    failSqlDescBuilder.append("--").append("脚本有更新数据操作，注意脚本尾部要进行表信息统计操作").append(System.lineSeparator());
                    failSqlDescBuilder.append("--").append("脚本有更新数据操作，注意脚本尾部要进行元数据刷新操作");
                }
                if (impalaBaseSqlVisitor.tem){
                    failSqlDescBuilder.append(System.lineSeparator()).append("--").append("脚本使用到了临时表，注意脚本尾部要显式删除临时表");
                }
                String sqlResult = failSqlDescBuilder.toString();
                failDescBuilder.append(sqlResult).append(System.lineSeparator()).append(sql).append(System.lineSeparator()).append(System.lineSeparator());
            }
            //将扫描结果导出文件
            exportSqlScanResult(failDescBuilder,fileName);
            UUID uuid = UUID.fastUUID();
            //设置文件扫描状态
            scanFileStatusRepository.save(new ScriptFileStatusEntity().setId(uuid.toString()).setFileName(fileName).setStatus("已扫描"));
        }
        //统计失败文件的个数
        getFailFileNum();
        return scanResultMap;
    }

    private void exportSqlScanResult(StringBuilder failDescBuilder, String fileName) {
       String filePath = sqlScanResultPath + "/" + fileName;
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 将扫描结果写入文件
            writer.write(failDescBuilder.toString());
            // 刷新缓冲区，确保所有内容都写入文件
            writer.flush();
        } catch (IOException e) {
            throw new RuntimeException("扫描结果导出失败",e);
        }
    }

    private Map<String, ScriptPackScanResultEntity> initScript(List<ScriptItemEntity> scriptItemEntityList, String fileName){
        List<ScriptPackScanResultEntity> scriptPackScanResultList;
        scriptPackScanResultList = turnToResult(scriptItemEntityList,fileName);
        return scriptPackScanResultList.stream()
                .map(s -> s.setScanState("success"))
                .map(s -> s.setScanTable("default"))
                .collect(Collectors.toMap(ScriptPackScanResultEntity::getScanItem, Function.identity()));
    }

    /**
     * @param scriptItemEntityList
     * @param fileName
     * @Method turnToResult
     * @Param
     * @Return java.util.List<com.hex.ds.code.detection.component.code.detection.model.ScriptPackScanResult>
     * @Description 模板对象维护为result对象
     * @Author Wang zhihao
     * @Date 2023/10/8 13:35
     * @Version V1.0
     */
    private List<ScriptPackScanResultEntity> turnToResult(List<ScriptItemEntity> scriptItemEntityList, String fileName) {
        List<ScriptPackScanResultEntity> scriptPackScanResults = new ArrayList<>();
        for (ScriptItemEntity scriptPackScanItem : scriptItemEntityList) {
            ScriptPackScanResultEntity scriptPackScanResult = new ScriptPackScanResultEntity()
                    .setScanName(scriptPackScanItem.getScanName())
                    .setFileName(fileName)
                    .setScanType(scriptPackScanItem.getScanType())
                    .setScanLevel(scriptPackScanItem.getScanLevel())
                    .setScanItem(scriptPackScanItem.getScanName())
                    .setScanItemDescribe(scriptPackScanItem.getScanDesc())
                    .setScanDescInfo(scriptPackScanItem.getScanDescInfo());
            scriptPackScanResults.add(scriptPackScanResult);
        }
        return scriptPackScanResults;
    }

    private List<ScriptItemEntity> readScanExcel(String scanExcelPath) {
        List<ScriptItemEntity> list = new ArrayList<>();
        FileInputStream excelFile = null;
        Workbook workbook = null;
        try {
            excelFile = new FileInputStream(scanExcelPath);
            workbook = WorkbookFactory.create(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);

            for (Row currentRow : datatypeSheet) {
                if (currentRow.getRowNum() == 0 || "N".equals(currentRow.getCell(4).getStringCellValue())) {
                    continue;
                }
                ScriptItemEntity scriptItem = new ScriptItemEntity();
                double numericCellValue = currentRow.getCell(0).getNumericCellValue();
                scriptItem.setId(String.valueOf(numericCellValue));
                scriptItem.setScanName(currentRow.getCell(1).getStringCellValue());
                scriptItem.setScanDesc(currentRow.getCell(2).getStringCellValue());
                scriptItem.setScanLevel(currentRow.getCell(3).getStringCellValue());
                scriptItem.setIsScan(currentRow.getCell(4).getStringCellValue());
                scriptItem.setScanDescInfo(currentRow.getCell(5).getStringCellValue());
                scriptItem.setScanType(currentRow.getCell(6).getStringCellValue());
                scriptItem.setHandComment(currentRow.getCell(7).getStringCellValue());
                scanItemRepository.save(scriptItem);
                list.add(scriptItem);
            }
        } catch (IOException e) {
            throw new RuntimeException("扫描项读取失败");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (excelFile != null) {
                try {
                    excelFile.close();
                } catch (IOException e) {
                    log.error("文件操作失败");
                }
            }
        }
        return list;
    }

    private Integer countDirectory(String filePaths) {
        File directory = new File(filePaths);
        int count = 0;
        File[] files = directory.listFiles();

        if (files != null) {
            for (File file : files) {
                if (file.isFile()) {
                    count++;
                }
            }
        }
        return count;
    }

    /**
     * 将文件信息存入数据库
     * @param fileObjectMap
     */
    private void readFileName(Map<String, File> fileObjectMap) {
        for (Map.Entry<String, File> entry : fileObjectMap.entrySet()) {
            String fileName = entry.getKey();
            UUID uuid = UUID.fastUUID();
            scanFileStatusRepository.save(new ScriptFileStatusEntity().setId(uuid.toString()).setFileName(fileName).setStatus("待扫描"));
        }
    }


    private Map<String, File> createFileObjectMap(Map<String, String> filePathMap) {
        Map<String, File> fileObjectMap = new HashMap<>();

        for (Map.Entry<String, String> entry : filePathMap.entrySet()) {
            String fileName = entry.getKey();
            String filePath = entry.getValue();
            File file = new File(filePath);
            fileObjectMap.put(fileName, file);
        }

        return fileObjectMap;
    }

    /**
     * 校验目录是否符合格式
     * @param scriptFilePath 脚本主目录
     */
    private void checkDirectory(String scriptFilePath) {
            File directory = new File(scriptFilePath);

            // 检查目录是否存在
            if (!directory.exists()) {
                throw new RuntimeException("脚本主目录不存在，请检查包内是否有script文件夹");
            }

            // 检查是否存在名为"impala"的文件夹
            File impalaDirectory = new File(directory, "impala");
            if (impalaDirectory.exists() && impalaDirectory.isDirectory()) {
                log.info("impala目录校验成功");
            } else {
                throw new RuntimeException("目录校验失败，不存在名为impala的文件夹");
            }
    }

    public List<String> getSqlByReadFile(String filePath, String fileName) {
        try {
            List<String> sqlList = new ArrayList<>();
            String sourceSql = cn.hutool.core.io.FileUtil.readUtf8String(filePath);
            // ① 用正则匹配的方式将被/*注释的sql语句剔除(多行注释的也可以)
            String noCommentSql = regexRemoveComment(sourceSql);
            // ② 去除被 --注释掉的行
            String noGeneralCommentSql = removeUnuseSql(noCommentSql);
            if (fileName.contains(".py")){
                // ③ 新的py截断方式
                sqlList = isqlExtractor.pyExtractor(noGeneralCommentSql);
            }
            if (sqlList.isEmpty()){
                //原有截断方式
                sqlList = isqlExtractor.sqlExtractor(noGeneralCommentSql);
            }
            return sqlList.stream().filter(s -> StrUtil.isNotEmpty(s.trim())).collect(Collectors.toList());
        } catch (Exception e) {
            log.error("脚本去除注释拆分文件出现异常：{}", e.getMessage(), e);
            throw new RuntimeException("脚本去除注释拆分文件出现异常：" + e.getMessage(), e);
        }
    }


    /**
     * @Method regexRemoveComment
     * @Param
     * @param sourceSql
     * @Return java.lang.String
     * @Description 用正则匹配的方式将/*与--类型的注释语句删除
     * @Author Wang zhihao
     * @Date 2023/10/27 10:54
     * @Version V1.0
     */
    private String regexRemoveComment(String sourceSql) {
        Pattern pattern = Pattern.compile("/\\*[\\s\\S]*?\\*/", Pattern.MULTILINE | Pattern.DOTALL);
        Matcher matcher = pattern.matcher(sourceSql);
        sourceSql = matcher.replaceAll("");
        return sourceSql;
    }

    private String removeUnuseSql(String sourceSql) {
        List<String> sqlLine = Arrays.stream(sourceSql.split("\n")).collect(Collectors.toList());
        List<String> usefulSqlLine = sqlLine.stream().filter(s -> !StrUtil.startWith(s.trim(), "--")).collect(Collectors.toList());
        return String.join("\n", usefulSqlLine);
    }
}
